<?php   if (!defined('BASEPATH')) exit('No direct script access allowed');


class Sostenitori_model extends CI_Model {
	
	private $params = array();
    function __construct()
    {
        parent::__construct();
		
    }

    function get_columns()
	{
		$sql = "SELECT column_name from information_schema.columns where
table_name='" . $this->db->dbprefix('sostenitori') . "'";
		$query = $this->db->query($sql);
		
		return $query->result_array();
	}
    
	function get_total($params = array())
	{
		
		if(empty($this->params))
		{
			$default_params = array
			(
				'where' => null,
				'group_by' => array()
			);
			
			foreach ($default_params as $key => $value)
			{
				$this->params[$key] = (!empty($params[$key]))? $params[$key]: $default_params[$key];
			}
		}
        
		
		$this->db->select('s.id');
		$this->db->from('sostenitori as s');
		$this->db->join('offerte as o', 'o.id_sostenitore = s.id', 'left');
		$this->db->join('titoli as t', 't.id = s.id_titolo', 'left');
		$this->db->group_by($this->params['group_by']);
		if(!is_null($this->params['where']))
		{
			$this->db->where( $this->params['where']);
		}
        //var_dump($this->params);
        $query = $this->db->get();
		$nb = $query->num_rows();
		return $nb;
	}
		

    function get_categorie()
    {
        $sql = "SELECT categoria FROM sostenitori GROUP BY categoria ORDER BY categoria";
        $query = $this->db->query($sql);
        
        return $query->result_array();
    }
    
	function get_stato_sostenitori()
    {
        $sql = "SELECT DISTINCT stato_sostenitore FROM sostenitori ORDER BY stato_sostenitore";
        $query = $this->db->query($sql);
        
        return $query->result_array();
    }
	
	function get_nazioni()
    {
        $sql = "SELECT DISTINCT nazione FROM sostenitori ORDER BY nazione";
        $query = $this->db->query($sql);
        
        return $query->result_array();
    }
	
	function get_list($params)
	{
        
		$default_params = array
		(
			'select' => 's.id, s.nomecognome, s.tel1, s.categoria, s.data_inserimento, t.nome as titolo',
			'where' => null,
			'limit' => null,
			'start' => 0,
			'order_by' => 's.id DESC',
			'group_by' => 's.id, s.nomecognome, s.tel1, s.categoria, s.data_inserimento, t.nome'
		);
        
		foreach ($default_params as $key => $value)
		{
			$params[$key] = (isset($params[$key]))? $params[$key]: $default_params[$key];
		}
        $this->params = $params;
		$this->db->select($params['select']);
		$this->db->from('sostenitori as s');
		$this->db->join('titoli as t', 't.id = s.id_titolo', 'left');
		$this->db->join('gruppi as g', 'g.id = s.id_gruppo', 'left');
		$this->db->join('offerte as o', 'o.id_sostenitore = s.id', 'left');
		$this->db->group_by($params['group_by']);
		if(!is_null($params['where']))
		{
			$this->db->where($params['where']);
		}
		if(!is_null($params['limit']))
		{
			$this->db->limit($params['limit'], $params['start']);
		}
		$this->db->order_by($params['order_by']);
		
		
        $query = $this->db->get();
        
		
		return $query->result_array();
	}


	function get_sostenitori_liberi()
	{
		$sql = "select s.* from sostenitori s where s.attivo=1 and lower(s.categoria)='adozione' and  s.id not in ( select distinct b.id_sostenitore from  adozioni as b where b.id_stato > 0)";
		
		$query = $this->db->query($sql);
		return $query->result_array();
	}	
	
	
	function get($params = array())
	{
	
		$default_params = array
		(
			'select' => 's.*',
			'where' => null
		);
		
		
		foreach ($default_params as $key => $value)
		{
			$params[$key] = (isset($params[$key]))? $params[$key]: $default_params[$key];
		}
	
		$sql = "SELECT " . $params['select'] . " FROM sostenitori as s
				LEFT OUTER JOIN titoli t ON s.id_titolo = t.id 
				LEFT OUTER JOIN gruppi g ON s.id_gruppo = g.id 
				LEFT OUTER JOIN cod_invio v ON s.id_cod_invio=v.id 
		WHERE " . $params['where'] . " LIMIT 1";
        
        
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0 )
		{
			$row = $query->row_array();
			return $row;
		}
		else
		{
			return false;
		}
	}	
	
	function save_image($data)
	{
		$this->db->insert('assistiti_foto', $data);
		//$this->cache->remove_group('image_list');
		return $this->db->insert_id();
		
	}
	
    function delete_image($id)
    {
        $this->db->where('id', $id);
        $this->db->delete('assistiti_foto');
    }

    function get_adozioni($params)
    {
	
		$default_params = array
		(
			'select' => 'ad.id as id_adozione,ad.data_inizio,t.nome as tipo,sa.nome as stato, s.id as id_sostenitore,s.nomecognome as sostenitore,m.id as id_missione, m.nome as missione',
			'where' => null,
            'order_by' => 'data_inizio'
		);
		
		
		foreach ($default_params as $key => $value)
		{
			$params[$key] = (isset($params[$key]))? $params[$key]: $default_params[$key];
		}
        
        $sql="select " . $params['select'] . " from adozioni ad left outer join tiposostegno t on ad.id_tiposostegno = t.id left outer join assistiti a on ad.id_assistito = a.id left outer join sostenitori s on ad.id_sostenitore = s.id left outer join missioni m on a.id_missione = m.id left outer join statoadozione sa on ad.id_stato = sa.id where " . $params['where'] . " order by " . $params['order_by'];
        
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0 )
		{
			
			return  $query->result_array();
		}
		else
		{
			return false;
		}
        
    
    }
    
	function get_totale_offerte_anno($id, $anno = 0)
	{
		if ($anno == 0) $anno = date("Y");
        $sql="sum(valore) as totale_l,sum(netto) as totale_n from offerte where trashed = 0 and id_sostenitore=" . $id ." and extract(year from data) as anno = '" . $anno . "' group by anno order by anno";
 		$query = $this->db->query($sql);
		if ($query->num_rows() > 0 )
		{
			return  $query->row_array();
		}
		else
		{
			return false;
		}
		
	}
	
	
    function get_totale_offerte_annuali($id)
    {
        $sql="select extract(year from data) as anno,sum(valore) as totale_l,sum(netto) as totale_n from offerte where trashed = 0 and id_sostenitore=" . $id ." group by anno order by anno";
 		$query = $this->db->query($sql);
		if ($query->num_rows() > 0 )
		{
			
			return  $query->result_array();
		}
		else
		{
			return false;
		}
       
    }
    
    function get_ultime_offerte($id, $start = 0, $limit = 20)
    {
 		$sql="select o.id_sostenitore, o.id as id_offerta,o.data as data, a.nomecognome as destinatario, m.nome as missione, o.valore as valore_l, o.netto as valore_n, c.nome as categoria from offerte o left outer join assistiti a on a.id = o.id_assistito left outer join missioni m on m.id = o.id_missione left outer join categorie c on c.id = o.id_categoria where o.trashed = 0 and o.id_sostenitore = $id order by data desc, o.id desc limit $limit offset $start ";	
 		$query = $this->db->query($sql);
		if ($query->num_rows() > 0 )
		{
			
			return  $query->result_array();
		}
		else
		{
			return false;
		}
       
    }
    function select_tipoassistito($sel = -1)
    {
        
        $out = "<select name='id_tipo'>\n";
        $sql = "select id,nome from tipoassistito order by LOWER(nome)";
        $query = $this->db->query($sql);
        $n = $query->num_rows();
        $rows = $query->result_array();
        for($i=0;$i<$n;$i++)
        {
            $r = $rows[$i];
            $selected = ($sel == $r['id']) ? 'selected' : '';
            $out .= "<option value='$r[id]' $selected>$r[nome]</option>\n";
        }
        $out .= "</select>\n";
        
        return $out;
    }
    
    function select_sesso($sel = 'M')
    {
        $out = "<select name=\"sesso\">\n";
        $selected = ($sel == 'M') ? 'selected' : '';
        $selected = ($sel == 'F') ? 'selected' : '';
        $out .= "<option value=\"M\" $selected>M</option>\n";
        $out .= "<option value=\"F\" $selected>F</option>\n";
        $out .= "</select>";
        return $out;

    }

    function get_titoli()
	{
		$this->db->order_by('nome');
		$query = $this->db->get('titoli');
		return $query->result_array();
	}
	
	
	function get_gruppi()
	{
		$this->db->where('attivo', 1);
		$this->db->order_by('nome');
		$query = $this->db->get('gruppi');
		
		return $query->result_array();
	
	}
	
	function get_codice_invio()
	{
		$this->db->order_by('nome');
		$query = $this->db->get('cod_invio');
		return $query->result_array();
	
	}

    function select_anni($nome='anni')
    {
    /* La funzione restituisce una select contenente una serie di anii che vanno da anno attuale -10 ad anno attuale*/
        $anno=date("Y");
        
        return ($this->select_generic_rev($nome,$anno-10,$anno));
    }

    function select_generic($name,$start,$end,$selected=-1)
    {
        $out = "<select name=\"$name\">";
        $out .= "<option></option>\n";
        for ($i=$start;$i<=$end;$i++)
        {
            $sel = ($selected == $i) ? 'selected' : '';
            $out .= "<option value=\"$i\" $sel>$i</option>\n";
        }
        $out .= "</select>";
        return $out;
    }

    function select_generic_rev($name,$start,$end,$selected=-1)
    {
        $out = "<select name=\"$name\">";
        $out .= "<option></option>\n";
        for ($i=$end;$i>=$start;$i--)
        {
            $sel = ($selected == $i) ? 'selected' : '';
            $out .= "<option value=\"$i\" $sel>$i</option>\n";
        }
        $out .= "</select>";
        return $out;
    }
 
    function save($data)
    {
        
   		if(!isset($data['id']) || !$data['id'])
        {
            $query = $this->db->query("SELECT max(id) as id FROM sostenitori");
            $row = $query->row_array();
            $data['id'] = $row['id']+1;
			
            $data['data_inserimento'] = date("Y-m-d");
			$data['attivo'] = 1;
            $this->db->insert('sostenitori', $data);
        }
        else
        {
            $this->db->where('id', $data['id']);
			
            $this->db->update('sostenitori', $data);
        }
		return $data['id'];

    }
    
    function activate($id)
    {
        $this->db->where('id', $id);
        $this->db->update('sostenitori', array('attivo' => 1));
    }
    
    function deactivate($id)
    {
        $this->db->where('id', $id);
        $this->db->update('sostenitori', array('attivo' => 0));
    }
}